/************************************************************************************/
/*  Program name CAPFNDIMP_CONC_PROG.sql                                            */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

ALTER TRIGGER FND_CONCURRENT_PROGRAMS_CT1 DISABLE;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDIMP_CONC_PROG';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_FND_CONC_PROG;

	CURSOR Cur_G IS
	SELECT
		A.*,
		B.Concurrent_Program_ID_N
	FROM
		CFND_CONCURRENT_PROGRAMS		A,
		CRST_FND_CONC_PROG_MAP			B
	--WHERE	A.Flex_Value_ID	=	B.Flex_Value_ID;
	WHERE	A.APPLICATION_ID = B.APPLICATION_ID
	AND A.CONCURRENT_PROGRAM_ID=B.CONCURRENT_PROGRAM_ID;
		
	lv_Next_Seq_ID	NUMBER;

BEGIN

	--  RENAME
	UPDATE
		FND_CONCURRENT_PROGRAMS
	SET		
		Concurrent_Program_Name	= NVL(CAPBRIMP_MAPPING.RenameStr(SUBSTR(Concurrent_Program_Name,1,4),Concurrent_Program_Name),
									 CAPBRIMP_MAPPING.RenameStr(SUBSTR(Concurrent_Program_Name,5,4),Concurrent_Program_Name))
	WHERE	Concurrent_Program_ID||application_id	IN	(
								SELECT
									Concurrent_Program_ID||application_id
								FROM
									CRST_FND_CONC_PROG
								);
								
	UPDATE
		FND_CONCURRENT_PROGRAMS
	SET		
		Concurrent_Program_Name	= CAPBRIMP_MAPPING.RenameStr(SUBSTR(Concurrent_Program_Name,5,4),Concurrent_Program_Name)
	WHERE	Concurrent_Program_ID||application_id	IN	(
								SELECT
									Concurrent_Program_ID||application_id
								FROM
									CRST_FND_CONC_PROG
								);

	FOR C1 IN Cur_F LOOP
			
	
		BEGIN
			SELECT	FND_CONCURRENT_PROGRAMS_S.NextVal
			INTO	lv_Next_Seq_ID
			FROM	DUAL;

			INSERT INTO CRST_FND_CONC_PROG_MAP VALUES
			(
			C1.APPLICATION_ID,
			C1.CONCURRENT_PROGRAM_ID,
			CAPBRIMP_MAPPING.RenameStr(SUBSTR(C1.CONCURRENT_PROGRAM_NAME,1,4),C1.CONCURRENT_PROGRAM_NAME),
			C1.APPLICATION_ID,
			lv_Next_Seq_ID,
			C1.CONCURRENT_PROGRAM_NAME
			);

			INSERT INTO FND_CONCURRENT_PROGRAMS
			VALUES
			(
			C1.APPLICATION_ID,
			lv_Next_Seq_ID,										--CONCURRENT_PROGRAM_ID,
			C1.CONCURRENT_PROGRAM_NAME,
			C1.LAST_UPDATE_DATE,
			CAPBRIMP_COMMON.GetUser(C1.LAST_UPDATED_BY_NAME),	--LAST_UPDATE_BY,
			C1.CREATION_DATE,
			CAPBRIMP_COMMON.GetUser(C1.CREATED_BY_NAME),		--CREATED_BY,
			C1.LAST_UPDATE_LOGIN,
			C1.EXECUTABLE_APPLICATION_ID,
			C1.EXECUTABLE_ID,
			C1.EXECUTION_METHOD_CODE,
			C1.ARGUMENT_METHOD_CODE,
			C1.QUEUE_CONTROL_FLAG,
			C1.QUEUE_METHOD_CODE,
			C1.REQUEST_SET_FLAG,
			C1.ENABLED_FLAG,
			C1.PRINT_FLAG,
			C1.RUN_ALONE_FLAG,
			C1.SRS_FLAG,
			C1.CLASS_APPLICATION_ID,
			C1.CONCURRENT_CLASS_ID,
			C1.EXECUTION_OPTIONS,
			C1.SAVE_OUTPUT_FLAG,
			C1.REQUIRED_STYLE,
			C1.OUTPUT_PRINT_STYLE,
			C1.PRINTER_NAME,
			C1.MINIMUM_WIDTH,
			C1.MINIMUM_LENGTH,
			C1.REQUEST_PRIORITY,
			C1.ATTRIBUTE_CATEGORY,
			C1.ATTRIBUTE1,
			C1.ATTRIBUTE2,
			C1.ATTRIBUTE3,
			C1.ATTRIBUTE4,
			C1.ATTRIBUTE5,
			C1.ATTRIBUTE6,
			C1.ATTRIBUTE7,
			C1.ATTRIBUTE8,
			C1.ATTRIBUTE9,
			C1.ATTRIBUTE10,
			C1.ATTRIBUTE11,
			C1.ATTRIBUTE12,
			C1.ATTRIBUTE13,
			C1.ATTRIBUTE14,
			C1.ATTRIBUTE15,
			C1.IPROG_ID,
			C1.OUTPUT_FILE_TYPE,
			C1.ENABLE_TRACE,
			C1.RESTART,
			C1.NLS_COMPLIANT,
			C1.ICON_NAME,
			C1.CD_PARAMETER,
			C1.INCREMENT_PROC,
			C1.MLS_EXECUTABLE_APP_ID,
			C1.MLS_EXECUTABLE_ID,
			C1.ENABLE_TIME_STATISTICS,
			C1.SECURITY_GROUP_ID,
			C1.STAT_COLLECT,
			C1.RESOURCE_CONSUMER_GROUP,
			C1.ROLLBACK_SEGMENT,
			C1.OPTIMIZER_MODE,
			C1.INSTANCE_ID,
			C1.REFRESH_PORTLET,
			C1.PROGRAM_TYPE,
			C1.ACTIVITY_SUMMARIZER,
			C1.REQUEST_SCH_INTERVAL,
			C1.ALLOW_MULTIPLE_PENDING_REQUEST,
			C1.DELETE_LOG_FILE
			);
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	BEGIN
		FOR C2 IN Cur_G LOOP
			INSERT INTO CFND_CONCURRENT_PROGRAMS VALUES
			(
				C2.Application_ID,
				C2.Concurrent_Program_ID_N,
				CAPBRIMP_MAPPING.GetOrgMapping(C2.S_ORG_ID),
				CAPBRIMP_MAPPING.GetBGMapping(C2.S_BUSINESS_GROUP_ID)
			);
		END LOOP;
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.Put_Line(SQLERRM);
			lv_Err_Count	:=	lv_Err_Count + 1;
	END;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/

ALTER TRIGGER FND_CONCURRENT_PROGRAMS_CT1 ENABLE;
